﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Threading.Tasks;
using Com.JunCaiSoft.AddressManage.Entity;
using Com.JunCaiSoft.AutoMapper;
using Com.JunCaiSoft.Common.Controller;
using Com.JunCaiSoft.Common.Enums;
using Com.JunCaiSoft.Common.Model;
using Com.JunCaiSoft.ConstructionCashFlow.Entity;
using Com.JunCaiSoft.ConstructionCashFlow.IDao;
using Com.JunCaiSoft.ConstructionCashFlow.Model;
using Com.JunCaiSoft.Hr.Entity;
using Microsoft.AspNetCore.Authorization;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Data.SqlClient;
using Newtonsoft.Json;

namespace Com.JunCaiSoft.API.ConstructionCashFlow.Controllers
{
    /// <summary>
    /// 项目信息管理
    /// </summary>
    [Route("api/[controller]/[action]")]
    [ApiController]
    [Authorize]
    public class ProjectController : BaseController<ProjectView,ProjectItem, Project,Guid>
    {
        public ProjectController(IProjectDao projectdao) : base(projectdao)
        {

        }
        public override async Task<ActionResult> DeleteSingleAsync(Guid Id)
        {
            try
            {
                await DaoAsync.dbContext.Database.BeginTransactionAsync();

                int result = await DaoAsync.DeleteListAsync<Project>(x => x.Id == Id);
                await DaoAsync.DeleteListAsync<ProjectSettlement>(x => x.SettlementProjectId == Id);
                await DaoAsync.DeleteListAsync<CashFlow>(x => x.ProjectId == Id);

                await DaoAsync.dbContext.Database.CommitTransactionAsync();
                return await Task.Run(() => Content(JsonConvert.SerializeObject(result)));
            }
            catch (Exception e)
            {
                await DaoAsync.dbContext.Database.RollbackTransactionAsync();
                throw e;
            }
        }
        /// <summary>
        /// 按项目ID批量删除
        /// </summary>
        /// <param name="DeleteList">项目ID列表</param>
        /// <returns></returns>
        [HttpPatch]
        public virtual async Task<ActionResult> DeleteList(IEnumerable<Guid> DeleteList)
        {
            try
            {
                await DaoAsync.dbContext.Database.BeginTransactionAsync();

                int result = await DaoAsync.DeleteListAsync<Project>(x => DeleteList.Contains(x.Id));
                await DaoAsync.DeleteListAsync<ProjectSettlement>(x => DeleteList.Contains(x.SettlementProjectId));
                await DaoAsync.DeleteListAsync<CashFlow>(x => DeleteList.Contains(x.ProjectId));

                await DaoAsync.dbContext.Database.CommitTransactionAsync();

                return await Task.Run(() => Content(JsonConvert.SerializeObject(result)));
            }
            catch (Exception e)
            {
                await DaoAsync.dbContext.Database.RollbackTransactionAsync();
                throw e;
            }
        }
        /// <summary>
        /// 分页查询
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        [HttpGet]
        public virtual async Task<ActionResult> ListByPageAsync(int pageIndex, int pageSize,String keyword)
        {
            try
            {
                var columns = @"a.Id,
	                            a.Num,
	                            a.NumCode,
	                            c.Name Province,
	                            d.Name City,
	                            e.Name Zone,
	                            p.Name Partment,
                                p.Boss PartmentLeader,
	                            a.Name,
                                a.Accountant,
                                a.Boss,
                                a.Mobile,
                                a.LandParcel,
	                            ROUND(b.EndBalance,2) EndBalance,
	                            ROUND(b.BeginFreezeMoney,2) FreezeMoney,
	                            ROUND(b.BeginFlowBalance,2) FlowBalance,
                                ROUND(b.BeginCashBalance,2) CashBalance,
                                ROUND(b.BeginCommercialTicket,2) CommercialTicket,
                                ROUND(b.BeginGongDiFang,2) GongDiFang,
                                g.Name ItemType,
	                            a.IsUsed,
	                            a.CreateTime ";

                var tables = @" Project a
	                            LEFT JOIN Administrative c ON a.Province = c.Code
	                            LEFT JOIN Administrative d ON a.City = d.Code
	                            LEFT JOIN Administrative e ON a.Zone = e.Code
	                            LEFT JOIN Partment p ON a.Partment = p.Id 
                                LEFT JOIN ProjectType g ON a.ItemType = g.Code,
	                            (
	                                SELECT
		                                * 
	                                FROM
		                                ( SELECT *, ROW_NUMBER () OVER ( PARTITION BY SettlementProjectId ORDER BY CreateTime ASC ) rn FROM ProjectSettlement a WHERE a.SettlementPeriod = 0  ) t 
	                                WHERE
		                                rn = 1 
	                            ) b ";

                var where = @" a.Id = b.SettlementProjectId ";

                List<SqlParameter> projectParams = new List<SqlParameter>() { };

                if (keyword != "" && keyword != null)
                {
                    where += " and a.Name like @ProjectName ";

                    SqlParameter QueryProjectName = new SqlParameter("@ProjectName", "%" + keyword + "%");

                    projectParams.Add(QueryProjectName);
                }

                var resultList = await DaoAsync.QuerySQLByPageQuickAsync<ProjectItem>(pageIndex, pageSize, " a.CreateTime desc ", columns, tables, where, projectParams.Select(x => ((ICloneable)x).Clone()).ToArray());

                return await Task.Run(() => Content(JsonConvert.SerializeObject(resultList)));
            }
            catch (Exception e)
            {
                throw e;
            }
        }
        [HttpPost]
        public override async Task<ActionResult> CreateAsync(ProjectView entity)
        {
            try
            {
                await DaoAsync.dbContext.Database.BeginTransactionAsync();

                var saveone = entity.MapTo<Project>();
                var result = await DaoAsync.InsertAsync(saveone);
                var settlement = entity.MapTo<ProjectSettlement>();
                settlement.SettlementProjectId = saveone.Id;
                await DaoAsync.InsertAsync(settlement);

                await DaoAsync.dbContext.Database.CommitTransactionAsync();
                return await Task.Run(() => Content(JsonConvert.SerializeObject(result)));
            }
            catch (Exception e)
            {
                await DaoAsync.dbContext.Database.RollbackTransactionAsync();
                throw e;
            }
        }
        [HttpPut]
        public override async Task<ActionResult> UpdateAsync(UpdateEntity<ProjectView> entityModel)
        {
            try
            {
                await DaoAsync.dbContext.Database.BeginTransactionAsync();

                var saveone = entityModel.entity.MapTo<Project>();
                var result = await DaoAsync.UpdateAsync(saveone, entityModel.Id);
                var settlement = entityModel.entity.MapTo<ProjectSettlement>();
                settlement.SettlementProjectId = saveone.Id;
                await DaoAsync.UpdateAsync(settlement, settlement.Id);

                await DaoAsync.dbContext.Database.CommitTransactionAsync();
                return await Task.Run(() => Content(JsonConvert.SerializeObject(result)));
            }
            catch(Exception e)
            {
                await DaoAsync.dbContext.Database.RollbackTransactionAsync();
                throw e;
            }
        }
        [HttpGet]
        public override async Task<ActionResult> GetAsync(Guid Id)
        {
            var columns = @"a.Id,
	                        a.Num,
	                        a.NumCode,
	                        a.Province,
	                        a.City,
	                        a.Zone,
	                        a.Partment,
	                        a.Name,
	                        a.ItemType,
                            a.Accountant,
                            a.Boss,
                            a.Mobile,
                            a.LandParcel,
                            a.DetailAddress,
                            b.Id SettlementProjectId,
                            b.BeginTime,
                            b.EndTime,
                            b.SettlementPeriod,
	                        ROUND(b.BeginBalance,2) BeginBalance,
	                        ROUND(b.EndBalance,2) EndBalance,
	                        ROUND(b.BeginFreezeMoney,2) FreezeMoney,
	                        ROUND(b.BeginFlowBalance,2) FlowBalance,
                            ROUND(b.BeginCashBalance,2) CashBalance,
                            ROUND(b.BeginCommercialTicket,2) CommercialTicket,
                            ROUND(b.BeginGongDiFang,2) GongDiFang,
                            b.IsSettlement,
	                        a.IsUsed,
	                        a.CreateTime ";

            var tables = @" Project a,
	                        (
	                            SELECT
		                            * 
	                            FROM
		                            ( SELECT *, ROW_NUMBER () OVER ( PARTITION BY SettlementProjectId ORDER BY CreateTime ASC ) rn FROM ProjectSettlement a WHERE a.SettlementPeriod = 0 ) t 
	                            WHERE
		                            rn = 1 
	                        ) b ";

            var where = @" a.Id = b.SettlementProjectId and a.Id = @Id ";

            SqlParameter parameter = new SqlParameter("@Id", Id);

            List<SqlParameter> arrary = new List<SqlParameter>();

            arrary.Add(parameter);

            var resultList = await DaoAsync.QuerySQLByPageQuickAsync<ProjectView>(1, 10, " a.CreateTime desc ", columns, tables, where, arrary.Select(x => ((ICloneable)x).Clone()).ToArray());



            var resultOne = resultList.items != null ? resultList.items.FirstOrDefault() : new ProjectView();

            return await Task.Run(() => Content(JsonConvert.SerializeObject(resultOne)));
        }
        /// <summary>
        /// 获取项目编码
        /// </summary>
        /// <returns></returns>
        [HttpGet]
        public virtual async Task<ActionResult> CreateProjectCode()
        {
            try
            {
                await DaoAsync.dbContext.Database.BeginTransactionAsync();

                int result = 0;

                return await Task.Run(() => Content(JsonConvert.SerializeObject(result)));
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                DaoAsync.dbContext.Database.CommitTransaction();
            }
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="ProjectNameOrProjectTail"></param>
        /// <returns></returns>
        [HttpGet]
        public virtual async Task<ActionResult> QueryCashFlowProject(String ProjectNameOrProjectTail)
        {
            try
            {
                var sql = @"select * from Project a
                                where 
                                a.Name like @ProjectName or a.NumCode like @ProjectCode ";
                List<SqlParameter> arrary = new List<SqlParameter>();

                SqlParameter para1 = new SqlParameter("@ProjectName", SqlDbType.VarChar);
                para1.Value = ProjectNameOrProjectTail != null ? "%" + ProjectNameOrProjectTail + "%" : "''";

                arrary.Add(para1);

                SqlParameter para2 = new SqlParameter("@ProjectCode", SqlDbType.VarChar);
                para2.Value = ProjectNameOrProjectTail != null ? "%" + ProjectNameOrProjectTail + "%" : "''";

                arrary.Add(para2);

                sql += "Order by a.CreateTime desc";

                var list = await DaoAsync.QuerySQLAsync<Project>(sql, arrary.Select(x => ((ICloneable)x).Clone()).ToArray());

                var companyList = list.ToList().Select(x => x.Partment).Distinct();

                List<CashFlowProject> resultList = new List<CashFlowProject>();

                int i = 0;

                foreach (var item in companyList)
                {
                    CashFlowProject one = new CashFlowProject();
                    one.OrderNum = i;
                    var company = DaoAsync.GetAsync<Company>(x => x.Id == item).Result;
                    one.Company = company != null ? company.Name : "";
                    one.Children = list.ToList().Where(x => x.Partment == item).ToList();
                    resultList.Add(one);
                    i++;
                }

                return await Task.Run(() => Content(JsonConvert.SerializeObject(resultList)));
            }
            catch (Exception e)
            {
                throw e;
            }
        }
    }
}
